﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using OfficeOpenXml;
using System.Xml;
using System.Drawing;
using OfficeOpenXml.Style;
namespace ARS.Common.Helper
{
    public class Data2ExeclHelper
    {



        public static string DataTable2Execl(DataTable dt, string fileName = "")
        {
            try
            {
                if (dt == null)
                {
                    return "";
                }
                var path = $"{Directory.GetCurrentDirectory()}/exportExcel";
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                string _fileName = fileName;
                if (string.IsNullOrEmpty(fileName))
                {
                    _fileName = $"{path}/{dt.TableName}{StringHelper.Getcode(8)}.xlsx";
                }
                using (var package = new ExcelPackage())
                {

                    var _tables = dt;
                    var _sheetName = _tables.TableName;
                    if (string.IsNullOrEmpty(_sheetName))
                    {
                        _sheetName = $"sheet1";
                    }
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(_sheetName);
                    //创建表头
                    var data = _tables.Columns;
                    var _rowcounts = _tables.Rows.Count;
                    for (var _coli = 0; _coli < _tables.Columns.Count; _coli++)
                    {
                        var _colinfo = _tables.Columns[_coli];
                        worksheet.Cells[1, _coli + 1].Value = _colinfo.ColumnName;
                        if (_colinfo.DataType == typeof(DateTime))
                        {
                            worksheet.Cells[1, _coli + 1, _rowcounts + 1, _coli + 1].Style.Numberformat.Format = "yyyy-mm-dd HH:MM:ss";
                        }
                        //添加样式 首行加粗
                        using (ExcelRange r = worksheet.Cells[1, _coli + 1])
                        {
                            r.Style.Font.SetFromFont(new Font("黑体", 11, FontStyle.Bold));
                            r.Style.Font.Color.SetColor(Color.Black);
                        }
                        //填充数据
                        for (var _rd = 0; _rd < _rowcounts; _rd++)
                        {
                            worksheet.Cells[_rd + 2, _coli + 1].Value = _tables.Rows[_rd][_coli];
                        }
                    }
                    var xlFile = new FileInfo(_fileName);
                    package.SaveAs(xlFile);
                    return xlFile.FullName;

                }
            }
            catch (Exception ex)
            {

                Console.WriteLine($"{ex.Message} \r\n{ex.StackTrace}");
                return "";
            }


        }

        /// <summary>
        /// DataSet导出数据
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static string DataSet2Execl(DataSet ds, string fileName = "")
        {
            try
            {
                if (ds == null || ds.Tables.Count < 1)
                {
                    return "";
                }
               // var path = $"{Environment.CurrentDirectory}/exportExcel";
                var path = $"{Directory.GetCurrentDirectory()}/exportExcel";
                Log.WriteLine($"DataSet2Execl 生成文件路径:{path}");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                string _fileName = fileName;
                if (string.IsNullOrEmpty(fileName))
                {
                    _fileName = $"{path}/{ds.DataSetName}{StringHelper.Getcode(8)}.xlsx";
                }
                using (var package = new ExcelPackage())
                {
                    for (var i = 0; i < ds.Tables.Count; i++)
                    {
                        var _tables = ds.Tables[i];
                        var _sheetName = _tables.TableName;
                        if (string.IsNullOrEmpty(_sheetName))
                        {
                            _sheetName = $"sheet{i + 1}";
                        }
                        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(_sheetName);
                        //创建表头
                        var data = _tables.Columns;
                        var _rowcounts = _tables.Rows.Count;
                        for (var _coli = 0; _coli < _tables.Columns.Count; _coli++)
                        {
                            var _colinfo = _tables.Columns[_coli];
                            worksheet.Cells[1, _coli + 1].Value = _colinfo.ColumnName;
                            if (_colinfo.DataType == typeof(DateTime))
                            {
                                worksheet.Cells[1, _coli + 1, _rowcounts + 1, _coli + 1].Style.Numberformat.Format = "yyyy-mm-dd HH:MM:ss";
                            }
                            //添加样式 首行加粗
                            using (ExcelRange r = worksheet.Cells[1, _coli + 1])
                            {
                                r.Style.Font.SetFromFont(new Font("Britannic Bold", 12, FontStyle.Bold));
                                r.Style.Font.Color.SetColor(Color.Black);
                            }
                            //填充数据
                            for (var _rd = 0; _rd < _rowcounts; _rd++)
                            {
                                worksheet.Cells[_rd + 2, _coli + 1].Value = _tables.Rows[_rd][_coli];
                            }
                        }

                    }
                    var xlFile = new FileInfo(_fileName);
                    package.SaveAs(xlFile);
                    return xlFile.FullName;

                }
            }
            catch (Exception ex)
            {

                Console.WriteLine($"{ex.Message} \r\n{ex.StackTrace}");
                return "";
            }

        }






    }
}
